package com.base.controller;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.base.comm.CommExcel;
import com.base.po.voip.ShopAliOrder;
import com.base.service.ShopAliOrderService;
import com.base.utils.Produce;

@Controller
@RequestMapping("/admin")
public class content_querendingdan {
	@Autowired
	public ShopAliOrderService shopAliOrderService;
	
	/**
     * 读取Excel文件
     * @param file 
     * @return
     * @throws IOException
     */
//	@RequestMapping(value = "/readExcel", method = RequestMethod.POST)
//    public void readExcel(HttpServletRequest request,HttpServletResponse response,@RequestParam("file") MultipartFile file) throws IOException,Exception{
//		PrintWriter out = response.getWriter();
//		File filePath = new File(file.getOriginalFilename());
//        if (file == null || CommExcel.EMPTY.equals(file.getOriginalFilename())) {
//            out.println("请传入正确的Excel文件");
//            return;
//        } else {
//            String postfix = getPostfix(file);
//            if (!CommExcel.EMPTY.equals(postfix)) {
//                if (CommExcel.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
//                  readXls(request,response,filePath);
//                } else if (CommExcel.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
//                	System.out.println(file);
//                    readXlsx(request,response,filePath);
//                }
//            } else {
//                System.out.println(filePath + CommExcel.NOT_EXCEL_FILE);
//            }
//        }
//    }
    
    public void readExcel(HttpServletRequest request,HttpServletResponse response,String file) throws Exception{
		
		PrintWriter out = response.getWriter();
        if (file == null || CommExcel.EMPTY.equals(file)) {
            out.println("请传入正确的Excel文件");
            return;
        } else {
            String postfix = getPostfix(file);
            if (!CommExcel.EMPTY.equals(postfix)) {
                if (CommExcel.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                  System.out.println(file);
                  readXls(file);
                } else if (CommExcel.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                	System.out.println(file);
                    readXlsx(file);
                }
            } else {
                System.out.println(file + CommExcel.NOT_EXCEL_FILE);
            }
        }
    }
	
    /**
     * 读取 Excel 2010
     * @param file
     * @return
     * @throws IOException
     */
    public void readXlsx(String file) throws Exception {
        System.out.println(CommExcel.PROCESSING + file);
        InputStream is = new FileInputStream(file);
        @SuppressWarnings("resource")
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        ShopAliOrder shopAliOrder = null;
        // 读取表格
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
               continue;
            }
            // 读取行
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                	shopAliOrder = new ShopAliOrder();
                    XSSFCell createtime = xssfRow.getCell(0);
                    XSSFCell clicktime = xssfRow.getCell(1);
                    XSSFCell title = xssfRow.getCell(2);
                    XSSFCell cid = xssfRow.getCell(3);
                    XSSFCell money = xssfRow.getCell(7);
                    XSSFCell jsmoney = xssfRow.getCell(14);
                    XSSFCell yjbl = xssfRow.getCell(17);
                    XSSFCell yjmoney = xssfRow.getCell(18);
                    XSSFCell ordernum = xssfRow.getCell(25);
                    //暂存广告位ID
                    XSSFCell userid = xssfRow.getCell(29);
                    //暂存广告位名称
                    XSSFCell agentid = xssfRow.getCell(30);
                    SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd"); 
                    shopAliOrder.setCreatetime(sf.parse(getXValue(createtime)));
                    shopAliOrder.setClicktime(sf.parse(getXValue(clicktime)));
                    shopAliOrder.setTitle(getXValue(title));
                    shopAliOrder.setCid(getXValue(cid));
                    shopAliOrder.setMoney(new BigDecimal(getXValue(money)));
                    shopAliOrder.setJsmoney(new BigDecimal(getXValue(jsmoney)));
                    shopAliOrder.setYjbl(new BigDecimal(getXValue(yjbl).substring(0, getXValue(yjbl).length()-2)));
                    shopAliOrder.setYjmoney(new BigDecimal(getXValue(yjmoney)));
                    shopAliOrder.setOrdernum(getXValue(ordernum));
                    shopAliOrder.setUserid(getXValue(userid));
                    shopAliOrder.setAgentid(getXValue(agentid));
                    CommExcel.list.add(shopAliOrder);
                }
            }
        }
    }
    
    /**
     * 读取 Excel 2003-2007
      * @param file
      * @return
      * @throws IOException
      * @throws ParseException 
      */
    public void readXls(String file) throws IOException, ParseException {
        System.out.println(CommExcel.PROCESSING + file);
        InputStream is = new FileInputStream(file);
        @SuppressWarnings("resource")
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        ShopAliOrder shopAliOrder = null;
        // 读取表格
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 读取行
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                	shopAliOrder = new ShopAliOrder();
                	HSSFCell createtime = hssfRow.getCell(0);
                    HSSFCell clicktime = hssfRow.getCell(1);
                    HSSFCell title = hssfRow.getCell(2);
                    HSSFCell cid = hssfRow.getCell(3);
                    HSSFCell money = hssfRow.getCell(7);
                    HSSFCell jsmoney = hssfRow.getCell(14);
                    HSSFCell yjbl = hssfRow.getCell(17);
                    HSSFCell yjmoney = hssfRow.getCell(18);
                    HSSFCell ordernum = hssfRow.getCell(25);
                    //暂存广告位ID
                    HSSFCell userid = hssfRow.getCell(29);
                    //暂存广告位名称
                    HSSFCell agentid = hssfRow.getCell(30);
                    SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd"); 
                    shopAliOrder.setCreatetime(sf.parse(getHValue(createtime)));
                    shopAliOrder.setClicktime(sf.parse(getHValue(clicktime)));
                    shopAliOrder.setTitle(getHValue(title));
                    shopAliOrder.setCid(getHValue(cid));
                    shopAliOrder.setMoney(new BigDecimal(getHValue(money)));
                    shopAliOrder.setJsmoney(new BigDecimal(getHValue(jsmoney)));
                    shopAliOrder.setYjbl(new BigDecimal(getHValue(yjbl).substring(0, getHValue(yjbl).length()-2)));
                    shopAliOrder.setYjmoney(new BigDecimal(getHValue(yjmoney)));
                    shopAliOrder.setOrdernum(getHValue(ordernum));
                    shopAliOrder.setUserid(getHValue(userid));
                    shopAliOrder.setAgentid(getHValue(agentid));
                    CommExcel.list.add(shopAliOrder);
                }
            }
        }
    }
    
    //Excal 2007-2010 取值
    @SuppressWarnings({ "static-access", "deprecation" })
    private String getXValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }
    
   //Excal 2003 取值
    @SuppressWarnings({ "static-access", "deprecation" })
    private String getHValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
           return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

 
    //判断Excel 2003 或者2007-2010
    public static String getPostfix(String file) {
    	
        if (file == null || CommExcel.EMPTY.equals(file)) {
            return CommExcel.EMPTY;
        }
        
        if (file.contains(CommExcel.POINT)) {
           return file.substring(file.lastIndexOf(CommExcel.POINT) + 1);
        }
        return CommExcel.EMPTY;
    }
    
    
    //跳转到确认订单页面
    @RequestMapping("/content_querendingdan")
    private ModelAndView index(HttpServletRequest request,HttpServletResponse response,String file) throws Exception {
    	response.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=UTF-8");
    	ModelAndView modelAndView =new ModelAndView();
    	
    	System.out.println(file);
    	readExcel(request,response,file);
    	
    	request.setAttribute("shopAliOrder", CommExcel.list);
    	
    	modelAndView.setViewName("jsp/content_querendingdan");
    	return modelAndView;
    	
	}
    
    //核对查看后返回
    @RequestMapping("/insertShopAliOrder")
    private void insertShopAliOrder(HttpServletRequest request, HttpServletResponse response) throws IOException {
    	response.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=UTF-8");
    	PrintWriter out = response.getWriter();
		String adminId = (String) request.getSession().getAttribute("adminUserId");
//		ShopAliOrder shopAliOrder = null;
		//核对数据库有 Excel没有的
		String orderNums = "";
		//插入成功条数
		int insternum = 0 ;
		//重复条数
		int num = 0 ;
//		ShopAliOrder sao = null;
		
		
		ShopAliOrder shop = null;
		
    	for(ShopAliOrder s : CommExcel.list) {
    		shop = shopAliOrderService.selectByOrderNum(s.getOrdernum());
    		
    		if(shop != null) {
    			orderNums += ","+ s.getOrdernum();
    			num++;
    			continue;
    		}
    		
    		shopAliOrderService.insertSelective(new ShopAliOrder(Produce.getUUID(), s.getCreatetime(),
        				s.getClicktime(), s.getTitle(),s.getCid(), s.getMoney(),new BigDecimal(0),
        				s.getJsmoney(),s.getYjbl(),s.getYjmoney(),s.getOrdernum(),adminId,true,0,0f,0f,null));
    		insternum++;
    	}
    	System.out.println(orderNums);
    	out.print("订单核对完成共"+insternum+"条订单,重复共"+num+"条订单,重复订单号"+orderNums);
	}
    
}
